package org.misalen.web.database.repository.mysql;

import java.util.List;

import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.AliasToBeanResultTransformer;
import org.misalen.utils.TextUtil;
import org.misalen.web.database.dao.mysql.impl.BaseMySqlDaoImpl;
import org.misalen.web.database.model.mysql.pro.ProAdvertOrder;
import org.misalen.web.database.util.PaginationFrom;
import org.springframework.stereotype.Repository;

@Repository
public class ProAdvertOrderRepository extends BaseMySqlDaoImpl<ProAdvertOrder> {

	public PaginationFrom<ProAdvertOrder> findByUser(String userId,
			PaginationFrom<ProAdvertOrder> pagination) {
		StringBuffer selectBuffer = new StringBuffer();
		selectBuffer.append("SELECT A.primaryKey,A.addTime,A.orderName,A.orderNum,A.startDate,A.stopDate,A.orderAmount,A.state");
		selectBuffer.append(" FROM ProAdvertOrder A ");
		selectBuffer.append(" WHERE A.userId = '" + userId + "' ");
		selectBuffer.append(" ORDER BY A.addTime desc");
		Session session = this.openSession();
		SQLQuery query = session.createSQLQuery(selectBuffer.toString());
		query.setResultTransformer(new AliasToBeanResultTransformer(ProAdvertOrder.class));
		StringBuffer countBuffer = new StringBuffer();
		countBuffer.append("select count(*) from (" + selectBuffer + ") T");
		pagination.setTotal(Long.valueOf(session.createSQLQuery(countBuffer.toString()).uniqueResult().toString()));
		setResultIndex(query, pagination.getPage(), pagination.getRows());
		@SuppressWarnings("unchecked")
		List<ProAdvertOrder> list = query.list();
		pagination.setList(list);
		closeSession(session);
		return pagination;
	}
	
	@Override
	public PaginationFrom<ProAdvertOrder> findPageByBean(ProAdvertOrder allclass, PaginationFrom<ProAdvertOrder> pagination) {
		Session session = this.openSession();
		String belongCity = allclass.getBelongCity();
		allclass.setBelongCity(null);
		String userName = allclass.getUserName();
		allclass.setUserName(null);
		String where = getHqlWhere(allclass);
		if(!TextUtil.isNullOrEmpty(belongCity) && !belongCity.equals("全国")) {
			where = where + "And u.belongCity = '" + belongCity + "' ";
		}
		if(!TextUtil.isNullOrEmpty(userName)) {
			where = where + "And u.userName like '%" + userName + "%' ";
		}
		
		String sql = "select a.*,u.userName as userName, u.belongCity as belongCity from ProAdvertOrder a left join ProOwnerUser u on a.userId=u.primaryKey "
				+ where + " order by addTime desc";
		SQLQuery query = session.createSQLQuery(sql);
		query.setResultTransformer(new AliasToBeanResultTransformer(allclass.getClass()));
		pagination.setTotal(Long.valueOf(session
				.createSQLQuery(
						"select count(*) from ProAdvertOrder a left join ProUser u on a.userId=u.primaryKey " + where)
				.uniqueResult().toString()));
		setResultIndex(query, pagination.getPage(), pagination.getRows());
		@SuppressWarnings("unchecked")
		List<ProAdvertOrder> list = query.list();
		pagination.setList(list);
		closeSession(session);
		return pagination;
	}
	
}